<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=9"/>
<meta name="generator" content="Doxygen 1.8.20"/>
<meta name="viewport" content="width=device-width, initial-scale=1"/>
<title>libxlsxwriter: Working with VBA Macros</title>
<link href="tabs.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="dynsections.js"></script>
<link href="search/search.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="search/searchdata.js"></script>
<script type="text/javascript" src="search/search.js"></script>
<link href="doxygen.css" rel="stylesheet" type="text/css" />
<link href="customdoxygen.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
<div id="titlearea">
<table cellspacing="0" cellpadding="0">
 <tbody>
 <tr style="height: 56px;">
  <td id="projectalign" style="padding-left: 0.5em;">
   <div id="projectname">libxlsxwriter
   </div>
  </td>
 </tr>
 </tbody>
</table>
</div>
<!-- end header part -->
<!-- Generated by Doxygen 1.8.20 -->
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
var searchBox = new SearchBox("searchBox", "search",false,'Search');
/* @license-end */
</script>
<script type="text/javascript" src="menudata.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
$(function() {
  initMenu('',true,false,'search.php','Search');
  $(document).ready(function() { init_search(); });
});
/* @license-end */</script>
<div id="main-nav"></div>
<!-- window showing the filter options -->
<div id="MSearchSelectWindow"
     onmouseover="return searchBox.OnSearchSelectShow()"
     onmouseout="return searchBox.OnSearchSelectHide()"
     onkeydown="return searchBox.OnSearchSelectKey(event)">
</div>

<!-- iframe showing the search results (closed by default) -->
<div id="MSearchResultsWindow">
<iframe src="javascript:void(0)" frameborder="0" 
        name="MSearchResults" id="MSearchResults">
</iframe>
</div>

</div><!-- top -->
<div class="PageDoc"><div class="header">
  <div class="headertitle">
<div class="title">Working with VBA Macros </div>  </div>
</div><!--header-->
<div class="contents">
<div class="toc"><h3>Table of Contents</h3>
<ul><li class="level1"><a href="#ww_macros_xlsm_format">The Excel XLSM file format</a></li>
<li class="level1"><a href="#ww_macros_include">How VBA macros are included in libxlsxwriter files</a></li>
<li class="level1"><a href="#ww_macros_extract">The vba_extract.py utility</a></li>
<li class="level1"><a href="#ww_macros_adding">Adding the VBA macros to a libxlsxwriter file</a></li>
<li class="level1"><a href="#ww_macros_codenames">Setting the VBA codenames</a></li>
<li class="level1"><a href="#ww_macros_debugging">What to do if it doesn&#39;t work</a></li>
</ul>
</div>
<div class="textblock"><p>This section explains how to add a VBA file containing functions or macros to an libxlsxwriter workbook.</p>
 <div class="fragment"><div class="line"><span class="preprocessor">#include &quot;xlsxwriter.h&quot;</span></div>
<div class="line"> </div>
<div class="line"><span class="keywordtype">int</span> main() {</div>
<div class="line"> </div>
<div class="line">    <a class="code" href="structlxw__workbook.html">lxw_workbook</a>  *workbook  = <a class="code" href="workbook_8h.html#a1cf96608a23ee4eb0e8467c15240d00b">workbook_new</a>(<span class="stringliteral">&quot;macro.xlsm&quot;</span>);</div>
<div class="line">    <a class="code" href="structlxw__worksheet.html">lxw_worksheet</a> *worksheet = <a class="code" href="workbook_8h.html#a81d456b4f65a464e78e4a0030ecc3c2e">workbook_add_worksheet</a>(workbook, NULL);</div>
<div class="line"> </div>
<div class="line">    <span class="comment">/* Add a macro that will execute when the file is opened. */</span></div>
<div class="line">    <a class="code" href="workbook_8h.html#a8de478eed94be65de0622c64a0179ff9">workbook_add_vba_project</a>(workbook, <span class="stringliteral">&quot;vbaProject.bin&quot;</span>);</div>
<div class="line"> </div>
<div class="line">    <a class="code" href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a>(worksheet, 0, 0, <span class="stringliteral">&quot;Overwrite this&quot;</span>, NULL);</div>
<div class="line"> </div>
<div class="line">    <span class="keywordflow">return</span> <a class="code" href="workbook_8h.html#ad9e7aeebc0fd43562db5bcd527b2ee5e">workbook_close</a>(workbook);</div>
<div class="line">}</div>
</div><!-- fragment --><div class="image">
<img src="macros.png" alt=""/>
</div>
<h1><a class="anchor" id="ww_macros_xlsm_format"></a>
The Excel XLSM file format</h1>
<p>An Excel <code>xlsm</code> file is exactly the same as an <code>xlsx</code> file except that is contains an additional <code>vbaProject.bin</code> file which contains functions and/or macros. Excel uses a different extension to differentiate between the two file formats since files containing macros are usually subject to additional security checks.</p>
<h1><a class="anchor" id="ww_macros_include"></a>
How VBA macros are included in libxlsxwriter files</h1>
<p>The <code>vbaProject.bin</code> file is a binary OLE COM container. This was the format used in older <code>xls</code> versions of Excel prior to Excel 2007. Unlike all of the other components of an xlsx/xlsm file the data isn't stored in XML format. Instead the functions and macros as stored as a pre-parsed binary format. As such it wouldn't be feasible to define macros and create a <code>vbaProject.bin</code> file from scratch (at least not in the remaining lifespan and interest levels of the author).</p>
<p>Instead a workaround is used to extract <code>vbaProject.bin</code> files from existing xlsm files and then add these to libxlsxwriter generated files.</p>
<h1><a class="anchor" id="ww_macros_extract"></a>
The vba_extract.py utility</h1>
<p>The <code>vba_extract.py</code> Python utility is used to extract the <code>vbaProject.bin</code> binary from an Excel 2007+ xlsm file. The utility is included in the libxlsxwriter examples directory: </p><pre class="fragment">$ python examples/vba_extract.py macro_file.xlsm
Extracted: vbaProject.bin
</pre><p>You can also install <code>vba_extract.py</code> into your system path by installing the Python xlsxwriter module: </p><pre class="fragment">$ pip install xlsxwriter
...
$ vba_extract.py
Utility to extract a vbaProject.bin binary from an
Excel 2007+ xlsm macro file ...
</pre><h1><a class="anchor" id="ww_macros_adding"></a>
Adding the VBA macros to a libxlsxwriter file</h1>
<p>Once the <code>vbaProject.bin</code> file has been extracted it can be added to the libxlsxwriter workbook using the <code><a class="el" href="workbook_8h.html#a8de478eed94be65de0622c64a0179ff9" title="Add a vbaProject binary to the Excel workbook.">workbook_add_vba_project()</a></code> function:</p>
<div class="fragment"><div class="line"><a class="code" href="workbook_8h.html#a8de478eed94be65de0622c64a0179ff9">workbook_add_vba_project</a>(workbook, <span class="stringliteral">&quot;./vbaProject.bin&quot;</span>);</div>
</div><!-- fragment --><dl class="section note"><dt>Note</dt><dd>The name doesn't have to be <code>vbaProject.bin</code>. Any suitable path/name for an existing VBA bin file will do.</dd></dl>
<p>If the VBA file contains functions you can then refer to them in calculations using <code><a class="el" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb" title="Write a formula to a worksheet cell.">worksheet_write_formula()</a></code>:</p>
<div class="fragment"><div class="line"><a class="code" href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a>(0, 0, <span class="stringliteral">&quot;=MyMortgageCalc(200000, 25)&quot;</span>)</div>
</div><!-- fragment --><p>Excel files that contain functions and macros should use an <code>xlsm</code> extension or else Excel will complain and possibly not open the file:</p>
<div class="fragment"><div class="line"><a class="code" href="structlxw__workbook.html">lxw_workbook</a> *workbook = new_workbook(<span class="stringliteral">&quot;macro.xlsm&quot;</span>);</div>
</div><!-- fragment --><h1><a class="anchor" id="ww_macros_codenames"></a>
Setting the VBA codenames</h1>
<p>VBA macros generally refer to workbook and worksheet objects. If the VBA codenames aren't specified explicitly then libxlsxwriter will use the Excel defaults of <code>ThisWorkbook</code> and <code>Sheet1</code>, <code>Sheet2</code> etc.</p>
<p>If the macro uses other codenames you can set them using the <code><a class="el" href="workbook_8h.html#a24dd6624ffdc3e17aa5bcfb4608755b8" title="Set the VBA name for the workbook.">workbook_set_vba_name()</a></code> and <code><a class="el" href="worksheet_8h.html#a8df6c0ce82172dafdd3e81923978bd4b" title="Set the VBA name for the worksheet.">worksheet_set_vba_name()</a></code> functions as follows:</p>
<div class="fragment"><div class="line"><span class="comment">// Set the VBA codenames for the workbook and any worksheets.</span></div>
<div class="line"><a class="code" href="workbook_8h.html#a24dd6624ffdc3e17aa5bcfb4608755b8">workbook_set_vba_name</a> (workbook,  <span class="stringliteral">&quot;MyWorkbook&quot;</span>);</div>
<div class="line"><a class="code" href="worksheet_8h.html#a8df6c0ce82172dafdd3e81923978bd4b">worksheet_set_vba_name</a>(worksheet, <span class="stringliteral">&quot;MySheet1&quot;</span>);</div>
<div class="line"><a class="code" href="worksheet_8h.html#a8df6c0ce82172dafdd3e81923978bd4b">worksheet_set_vba_name</a>(worksheet, <span class="stringliteral">&quot;MySheet2&quot;</span>);</div>
</div><!-- fragment --><dl class="section note"><dt>Note</dt><dd>This step is particularly important for macros created with non-English versions of Excel.</dd></dl>
<p>You can find the names that are used in the VBA editor or by unzipping the <code>xlsm</code> file and grepping the files. The following shows how to do that using <a href="http://xmlsoft.org/xmllint.html">libxml's xmllint</a> to format the XML for clarity: </p><pre class="fragment">$ unzip myfile.xlsm -d myfile
$ xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"

  &lt;workbookPr codeName="MyWorkbook" defaultThemeVersion="124226"/&gt;
  &lt;sheetPr codeName="MySheet1"/&gt;
</pre><h1><a class="anchor" id="ww_macros_debugging"></a>
What to do if it doesn't work</h1>
<p>The libxlsxwriter test suite contains several tests to ensure that this feature works and there is a working example as shown above. However, there is no guarantee that it will work in all cases. Some effort may be required and some knowledge of VBA will certainly help. If things don't work out here are some things to try:</p>
<ol type="1">
<li>Start with a simple macro file, ensure that it works and then add complexity.</li>
<li>Check the code names that macros use to refer to the workbook and worksheets (see the previous section above). In general VBA uses a code name of <code>ThisWorkbook</code> to refer to the current workbook and the sheet name (such as <code>Sheet1</code>) to refer to the worksheets. These are the defaults used by libxlsxwriter. If the macro uses other names, or the macro was extracted from an non-English language version of Excel, then you can specify the appropriate names using the <code><a class="el" href="workbook_8h.html#a24dd6624ffdc3e17aa5bcfb4608755b8" title="Set the VBA name for the workbook.">workbook_set_vba_name()</a></code> and <code><a class="el" href="worksheet_8h.html#a8df6c0ce82172dafdd3e81923978bd4b" title="Set the VBA name for the worksheet.">worksheet_set_vba_name()</a></code> functions: <div class="fragment"><div class="line"><span class="comment">// Set the VBA codenames for the workbook and any worksheets.</span></div>
<div class="line"><a class="code" href="workbook_8h.html#a24dd6624ffdc3e17aa5bcfb4608755b8">workbook_set_vba_name</a> (workbook,  <span class="stringliteral">&quot;MyWorkbook&quot;</span>);</div>
<div class="line"><a class="code" href="worksheet_8h.html#a8df6c0ce82172dafdd3e81923978bd4b">worksheet_set_vba_name</a>(worksheet, <span class="stringliteral">&quot;MySheet1&quot;</span>);</div>
<div class="line"><a class="code" href="worksheet_8h.html#a8df6c0ce82172dafdd3e81923978bd4b">worksheet_set_vba_name</a>(worksheet, <span class="stringliteral">&quot;MySheet2&quot;</span>);</div>
</div><!-- fragment --></li>
<li>Try to extract the macros from an Excel 2007 file. The method should work with macros from later versions (it was also tested with Excel 2010 macros). However there may be features in the macro files of more recent version of Excel that aren't backward compatible.</li>
</ol>
<p>Next: <a class="el" href="examples.html">Example Programs</a> </p>
</div></div><!-- contents -->
</div><!-- PageDoc -->
<div class="ttc" id="aworkbook_8h_html_ad9e7aeebc0fd43562db5bcd527b2ee5e"><div class="ttname"><a href="workbook_8h.html#ad9e7aeebc0fd43562db5bcd527b2ee5e">workbook_close</a></div><div class="ttdeci">lxw_error workbook_close(lxw_workbook *workbook)</div><div class="ttdoc">Close the Workbook object and write the XLSX file.</div></div>
<div class="ttc" id="aworksheet_8h_html_a8df6c0ce82172dafdd3e81923978bd4b"><div class="ttname"><a href="worksheet_8h.html#a8df6c0ce82172dafdd3e81923978bd4b">worksheet_set_vba_name</a></div><div class="ttdeci">lxw_error worksheet_set_vba_name(lxw_worksheet *worksheet, const char *name)</div><div class="ttdoc">Set the VBA name for the worksheet.</div></div>
<div class="ttc" id="aworksheet_8h_html_ae57117f04c82bef29805ec3eabc219bb"><div class="ttname"><a href="worksheet_8h.html#ae57117f04c82bef29805ec3eabc219bb">worksheet_write_formula</a></div><div class="ttdeci">lxw_error worksheet_write_formula(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *formula, lxw_format *format)</div><div class="ttdoc">Write a formula to a worksheet cell.</div></div>
<div class="ttc" id="aworkbook_8h_html_a1cf96608a23ee4eb0e8467c15240d00b"><div class="ttname"><a href="workbook_8h.html#a1cf96608a23ee4eb0e8467c15240d00b">workbook_new</a></div><div class="ttdeci">lxw_workbook * workbook_new(const char *filename)</div><div class="ttdoc">Create a new workbook object.</div></div>
<div class="ttc" id="astructlxw__worksheet_html"><div class="ttname"><a href="structlxw__worksheet.html">lxw_worksheet</a></div><div class="ttdoc">Struct to represent an Excel worksheet.</div><div class="ttdef"><b>Definition:</b> worksheet.h:1603</div></div>
<div class="ttc" id="aworkbook_8h_html_a8de478eed94be65de0622c64a0179ff9"><div class="ttname"><a href="workbook_8h.html#a8de478eed94be65de0622c64a0179ff9">workbook_add_vba_project</a></div><div class="ttdeci">lxw_error workbook_add_vba_project(lxw_workbook *workbook, const char *filename)</div><div class="ttdoc">Add a vbaProject binary to the Excel workbook.</div></div>
<div class="ttc" id="astructlxw__workbook_html"><div class="ttname"><a href="structlxw__workbook.html">lxw_workbook</a></div><div class="ttdoc">Struct to represent an Excel workbook.</div><div class="ttdef"><b>Definition:</b> workbook.h:273</div></div>
<div class="ttc" id="aworkbook_8h_html_a24dd6624ffdc3e17aa5bcfb4608755b8"><div class="ttname"><a href="workbook_8h.html#a24dd6624ffdc3e17aa5bcfb4608755b8">workbook_set_vba_name</a></div><div class="ttdeci">lxw_error workbook_set_vba_name(lxw_workbook *workbook, const char *name)</div><div class="ttdoc">Set the VBA name for the workbook.</div></div>
<div class="ttc" id="aworksheet_8h_html_ac208046e7a6d12cc87982422efa41b31"><div class="ttname"><a href="worksheet_8h.html#ac208046e7a6d12cc87982422efa41b31">worksheet_write_string</a></div><div class="ttdeci">lxw_error worksheet_write_string(lxw_worksheet *worksheet, lxw_row_t row, lxw_col_t col, const char *string, lxw_format *format)</div><div class="ttdoc">Write a string to a worksheet cell.</div></div>
<div class="ttc" id="aworkbook_8h_html_a81d456b4f65a464e78e4a0030ecc3c2e"><div class="ttname"><a href="workbook_8h.html#a81d456b4f65a464e78e4a0030ecc3c2e">workbook_add_worksheet</a></div><div class="ttdeci">lxw_worksheet * workbook_add_worksheet(lxw_workbook *workbook, const char *sheetname)</div><div class="ttdoc">Add a new worksheet to a workbook.</div></div>
<!-- HTML footer for doxygen 1.8.20-->
<!-- start footer part -->
<hr class="footer"/><address class="footer"><small>
Copyright 2014-2020 John McNamara.
Generated by&#160;<a href="http://www.doxygen.org/index.html"><img class="footer" src="doxygen.svg" width="104" height="31" alt="doxygen"/></a> 1.8.20
</small></address>
</body>
</html>
